package com.netcracker.training.automonic.dao.entities.oracle;

import static com.netcracker.training.automonic.dao.entities.oracle.SQLQueries.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.netcracker.training.automonic.dao.entities.Attribute;
import com.netcracker.training.automonic.dao.entities.AttributeType;
import com.netcracker.training.automonic.dao.entities.Object;
import com.netcracker.training.automonic.dao.factories.oracle.OracleDAOFactory;

/**
 * Concrete implementation for Oracle Database Express Edition 11g Release 2.
 * 
 * @author Vadim Gidulyan
 * 
 * @see AttributeType
 */

public class OracleAttributeType implements AttributeType // TODO Javadoc
{

	public static final Logger LOGGER = Logger.getLogger(OracleSite.class);
	
	private static OracleDAOFactory daoFactory = new OracleDAOFactory();
	private static Connection con = getConnection();
	
	private static Connection getConnection()
	{
		if (con == null)
			con = daoFactory.createConnection();
		return con;
	}
	
	public static AttributeType createAttributeType(String code, String name)
	{
		AttributeType attributeType = null;
		
		try (Statement st = con.createStatement())
		{
			String sql = "SELECT seq_AttributeTypes.NEXTVAL FROM DUAL";
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				int id = rs.getInt(1);
				attributeType = new OracleAttributeType(id, code, name);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(attributeType);
		}
		
		return attributeType;
	}
	
	public static AttributeType selectAttributeTypeById(int id)
	{
		AttributeType attributeType = null;
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTE_TYPE_BY_ID, id);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				attributeType = new OracleAttributeType(
						id,
						rs.getString("code"),
						rs.getString("name"));
			}			
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(attributeType);
		}
		
		return attributeType;
	}

	public static AttributeType selectAttributeTypeByCode(String code)
	{
		AttributeType attributeType = null;
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTE_TYPE_BY_CODE, code);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				attributeType = new OracleAttributeType(
						rs.getInt("attrtype_id"),
						code,
						rs.getString("name"));
			}			
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(attributeType);
		}
		
		return attributeType;
	}

	public static List<AttributeType> selectAttributeTypesByName(String name)
	{
		List<AttributeType> attributeTypes = new LinkedList<AttributeType>();
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTE_TYPES_BY_NAME, name);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				AttributeType attributeType = 
						new OracleAttributeType(
								rs.getInt("attrtype_id"),
								rs.getString("code"),
								name);
				attributeTypes.add(attributeType);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (AttributeType attributeType : attributeTypes)
					LOGGER.trace(attributeType);
		}
		
		return attributeTypes;
	}
	
	public static List<AttributeType> selectAllAttributeTypes()
	{
		List<AttributeType> attributeTypes = new LinkedList<AttributeType>();
		
		try (Statement stmt = con.createStatement())
		{
			ResultSet rs = stmt.executeQuery(SELECT_ALL_ATTRIBUTE_TYPES);
			
			while (rs.next())
			{
				AttributeType attributeType = 
						new OracleAttributeType(rs.getInt("attrtype_id"),
						                        rs.getString("code"),
						                        rs.getString("name"));
				attributeTypes.add(attributeType);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (AttributeType attributeType : attributeTypes)
					LOGGER.trace(attributeType);
		}
		
		return attributeTypes;
	}
	
	private final int id;
	private String code = "",
	               name = "";
	
	private boolean modified;
	
	// CONSTRUCTORS
	
	protected OracleAttributeType(int id, String code, String name)
	{
		this.id = id;
		setCode(code);
		setName(name);
		
		modified = false;
	}
	
	// ACCESSORS
	
	@Override
	public int getId()
	{
		return id;
	}

	@Override
	public String getCode()
	{
		return code;
	}

	@Override
	public void setCode(String code)
	{
		if (this.code.hashCode() != code.hashCode())
			modified = true;
		code = code.replaceAll("'{1,}", "''");
		this.code = code;
	}

	@Override
	public String getName()
	{
		return name;
	}

	@Override
	public void setName(String name)
	{
		if (this.name.hashCode() != name.hashCode())
			modified = true;
		name = name.replaceAll("'{1,}", "''");
		this.name = name;
	}

	/**
	 * Not implemented.
	 * 
	 * @return null
	 */
	@Override
	@Deprecated
	public List<Object> getObjects()
	{
		return null;
	}

	@Override
	public List<Attribute> getAttributes()
	{
		return OracleAttribute.selectAttributesByType(this);
	}
	
	// METHODS
	
	@Override
	public String toString()
	{
		return String.format("%d [%s] %s", id, code, name);
	}
	
	@Override
	public void save()
	{
		boolean attributeTypeExists = (selectAttributeTypeById(id) != null);
		
		if (attributeTypeExists)
		{
			if (modified)
			{
				String sql = String.format(
						"UPDATE AttributeTypes "
					  + "SET code = '%s',"
					  + "    name = '%s' "
					  + "WHERE attrtype_id = %d",
						code, name, id);
				
				try (Statement st = con.createStatement())
				{
					st.executeUpdate(sql);
				}
				catch (SQLException e)
				{
					LOGGER.error(null, e);
					e.printStackTrace();
				}
				finally
				{
					if (LOGGER.isTraceEnabled())
						LOGGER.trace(sql);
				}
			}
		}
		else
		{
			String sql = String.format(
					"INSERT INTO AttributeTypes "
				  + "	VALUES (%d, '%s', '%s')",
					id, code, name);
			
			try (Statement st = con.createStatement())
			{
				st.executeUpdate(sql);
			}
			catch (SQLException e)
			{
				LOGGER.error(null, e);
				e.printStackTrace();
			}
			finally
			{
				if (LOGGER.isTraceEnabled())
					LOGGER.trace(sql);
			}
		}
		
		modified = false;
		LOGGER.info("Saved attribute type: " + this);
	}

}
